SET NOCOUNT ON;
go
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name='Fill_Ring_Buffer_Scheduler_Monitor'
	and schema_id = schema_id('performance'))
BEGIN
	DROP PROCEDURE performance.Fill_Ring_Buffer_Scheduler_Monitor;
	delete metadata.utilityInfo 
	where
	info_desc='performance.Fill_Ring_Buffer_Scheduler_Monitor'
END
GO
/*********************************************************************************************************
*Created/Source by	:sfibich
*ScriptName			:performance.proc.Fill_Ring_Buffer_Scheduler_Monitor.sql													
*Version			:1.0.120813
*Purpose			:Fills the performance.Ring_Buffer_Scheduler_Monitor table with data
*					 from the Ring_Buffer DMV, RING_BUFFER_SCHEDULER_MONITOR type data.
*					 I belive I saw something very similar on Jonathan Kehayias blog, this
*					 however is my code.  (I got the idea from him).  The RING_BUFFER_SCHEDULER_MONITOR
*					 events occur every minute.  The timestamp to datetime can become incorrect
*					 if the computer goes in to a sleep mode or other lower power mode.
*					 
*********************************************************************************************************/
create procedure performance.Fill_Ring_Buffer_Scheduler_Monitor
as
BEGIN
insert into performance.Ring_Buffer_Scheduler_Monitor
select
dateadd(s,-.001*dosi.cpu_ticks/(dosi.cpu_ticks/dosi.ms_ticks)-.001*dorb.timestamp ,getdate()) as [Record_Date_Time]
,x.base.value('./ProcessUtilization[1]','int') as [Process_Utilization_Percent]
,x.base.value('./SystemIdle[1]','int') as [System_Idle_Percent]
,x.base.value('./MemoryUtilization[1]','int') as [Memory_Utilization_Percent]
,x.base.value('./PageFaults[1]','int') as [Page_Faults]
,x.base.value('./WorkingSetDelta[1]','int') as [Working_Set_Delta]
from
(
	select
	dorb.timestamp
	,convert(XML,dorb.record) as [record]
	from
	sys.dm_os_ring_buffers dorb
	where
	dorb.ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR'
) as dorb
cross apply
dorb.record.nodes('//Record/SchedulerMonitorEvent/SystemHealth') as x(base)
cross join
sys.dm_os_sys_info dosi
inner join
(
select 
coalesce(max(rbsm.Record_Date_Time),'1/1/1900') as [Record_Date_Time]
from 
performance.Ring_Buffer_Scheduler_Monitor rbsm
)
rbsm
on
dateadd(ms,.334
	,dateadd(s,-.001*dosi.cpu_ticks/(dosi.cpu_ticks/dosi.ms_ticks)-.001*dorb.timestamp,getdate())
) > rbsm.Record_Date_Time; 
END
go
if exists (select 1 from sys.objects where name='Fill_Ring_Buffer_Scheduler_Monitor' and SCHEMA_NAME(schema_id)='performance')
BEGIN
	merge metadata.utilityInfo as t
	using (
	select 
	'performance.Fill_Ring_Buffer_Scheduler_Monitor' as info_desc
	,'1.0.120813' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
END